package com.itnature.testchip.process;

import java.io.FileOutputStream;
import java.io.OutputStream;
import java.util.Vector;

import org.apache.log4j.Logger;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;

import com.itnature.testchip.business.HeaderFooter;
import com.itnature.testchip.business.StopBin;
import com.itnature.testchip.business.TFDoc;
import com.itnature.testchip.business.TestSuite;
import com.itnature.testchip.business.Timing;
import com.itnature.testchip.business.VDDDetail;
import com.itnature.testchip.business.VDDSummary;

public class ExcelFileProcessing {
	
	String outputFile = "";
	
	HSSFWorkbook book;
	
	static ExcelFileProcessing singleton;

	public static void createUniqueInstance(String outputFilePath) {
		if(singleton == null) {
			singleton = new ExcelFileProcessing();
		}
		if(outputFilePath.indexOf(".xls") < 0 ) {
			outputFilePath = outputFilePath+ ".xls";
		}
		singleton.outputFile = outputFilePath;
	}
	
	public static ExcelFileProcessing get() {
		if(singleton == null) {
			throw new RuntimeException("you must call createUniqueInstance() fist");
		}
		return singleton;
	}

	public ExcelFileProcessing() {
		book = new HSSFWorkbook();
	}
	
	public void buildTFDOCSheet(TFDoc tfDoc) {
		//use for set bold style
		HSSFFont font = book.createFont();
		font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
		HSSFCellStyle style = book.createCellStyle();
		style.setFont(font);
		
		HSSFSheet sheet = book.createSheet("tfdoc");
		sheet.setDefaultColumnWidth((short) 15);
		short[] sizes = {5500};
		//build header
		HeaderFooter hf = tfDoc.getHeaderFooter();
		sheet.createRow(0).createCell((short) 0).setCellValue("Device: "+hf.getDevice());
		sheet.getRow(0).getCell((short) 0).setCellStyle(style);
		sheet.createRow(1).createCell((short) 0).setCellValue("Test program name: "+hf.getProgramName());
		sheet.getRow(1).getCell((short) 0).setCellStyle(style);
		sheet.createRow(2).createCell((short) 0).setCellValue("Revision: "+hf.getRevision());
		sheet.getRow(2).getCell((short) 0).setCellStyle(style);
		sheet.createRow(3).createCell((short) 0).setCellValue("Date: "+hf.getDate());
		sheet.getRow(3).getCell((short) 0).setCellStyle(style);
		
		//build header for body table
		sheet.createRow(4).createCell((short) 0).setCellValue("");
		String[] headerName = { "Test Name","Bin Name", "Soft Bin", "Hard bin", "Vdd",
				"Timing", "Frequency (Mhz)", "Timing Equation", "Timing Spec",
				"Timing Set", "Level Equation", "Level Spec", "Level Set",
				"Analog Set", "Testfunction", "Vector Label" };
		HSSFRow row = sheet.createRow(5);
		for(short i=0 ; i<headerName.length ; i++) {
			row.createCell(i).setCellValue(headerName[i]);
			sheet.getRow(5).getCell(i).setCellStyle(style);
		}
		int rowIdx = 6;
		row = sheet.createRow(rowIdx++);
		Vector<StopBin> stopbins = tfDoc.getStopBins();
		for(int i=0 ; i<stopbins.size() ; i++) {
			StopBin stopbin = stopbins.get(i);
			TestSuite testSuite = tfDoc.getTestSuites().get(i);
			/*
			if(testSuite.getTimingEquation().length() == 0) {
				short cellIdx = 0 ;
				try{
					row.createCell(cellIdx++).setCellValue(stopbin.getTestName());
					row.createCell(cellIdx++).setCellValue(stopbin.getBinName());
					row.createCell(cellIdx++).setCellValue(new Double(stopbin.getSoftBin()).doubleValue());
					row.createCell(cellIdx++).setCellValue(new Double(stopbin.getHardBin()).doubleValue());
				}catch (Exception e) {
					e.printStackTrace();
				}
				
				row = sheet.createRow(rowIdx++);
				//continue;
			}
*/
			
			short cellIdx = 0 ;
			row.createCell(cellIdx++).setCellValue(stopbin.getTestName());
			row.createCell(cellIdx++).setCellValue(stopbin.getBinName());
			try {
				Double d = new Double(stopbin.getSoftBin());
				row.createCell(cellIdx++).setCellValue(d.doubleValue());
			} catch (Exception e) {
				row.createCell(cellIdx++).setCellValue(stopbin.getSoftBin());
			}
			try {
				Double d = new Double(stopbin.getHardBin());
				row.createCell(cellIdx++).setCellValue(d.doubleValue());	
			} catch (Exception e) {
				row.createCell(cellIdx++).setCellValue(stopbin.getHardBin());
			}
			
			
			if(i < tfDoc.getTimings().size()) {
				Timing timing = tfDoc.getTimings().get(i);
				row.createCell(cellIdx++).setCellValue(timing.getVdd());
				row.createCell(cellIdx++).setCellValue(timing.getTimming());
				if(timing.getFreq() == null || timing.getFreq().length()==0) {
					row.createCell(cellIdx++).setCellValue("");	
				}else {
					try {
						row.createCell(cellIdx++).setCellValue(new Double(timing.getFreq()).doubleValue());
					} catch (Exception e) {
						row.createCell(cellIdx++).setCellValue(timing.getFreq());
					}
				}
			}else {
				row.createCell(cellIdx++).setCellValue("");
				row.createCell(cellIdx++).setCellValue("");
				row.createCell(cellIdx++).setCellValue("");
			}
			
			
			if(testSuite.getTimingEquation().length() != 0) {
				try {
					Double tmp =new Double(testSuite.getTimingEquation()).doubleValue(); 
					row.createCell(cellIdx++).setCellValue(tmp);	
				} catch (Exception e) {
					row.createCell(cellIdx++).setCellValue(testSuite.getTimingEquation().replace("\"", ""));
				}
			}else {
				row.createCell(cellIdx++).setCellValue("");	
			}
			
			if(testSuite.getTimingSpec().length() != 0) {
				try {
					Double tmp = new Double(testSuite.getTimingSpec()).doubleValue();
					row.createCell(cellIdx++).setCellValue(tmp);					
				} catch (Exception e) {
					row.createCell(cellIdx++).setCellValue(testSuite.getTimingSpec().replace("\"", ""));
				}
			}else {
				row.createCell(cellIdx++).setCellValue("");
			}
			
			if(testSuite.getTimingSet().length() != 0) {
				try {
					Double tmp = new Double(testSuite.getTimingSet()).doubleValue();
					row.createCell(cellIdx++).setCellValue(tmp);					
				} catch (Exception e) {
					row.createCell(cellIdx++).setCellValue(testSuite.getTimingSet().replace("\"", ""));
				}
			}else {
				row.createCell(cellIdx++).setCellValue("");
			}
			
			
			if(testSuite.getLevelEquation().length() != 0) {
				try {
					Double tmp = new Double(testSuite.getLevelEquation()).doubleValue();
					row.createCell(cellIdx++).setCellValue(tmp);					
				} catch (Exception e) {
					row.createCell(cellIdx++).setCellValue(testSuite.getLevelEquation().replace("\"", ""));
				}
			}else {
				row.createCell(cellIdx++).setCellValue("");
			}
			
			
			if(testSuite.getLevelSpec().length() != 0) {
				try {
					Double tmp = new Double(testSuite.getLevelSpec()).doubleValue();
					row.createCell(cellIdx++).setCellValue(tmp);	
				} catch (Exception e) {
					row.createCell(cellIdx++).setCellValue(testSuite.getLevelSpec().replace("\"", ""));
				}
			}else {
				row.createCell(cellIdx++).setCellValue("");
			}
			
			
			if(testSuite.getLevelSet().length() != 0) {
				try {
					Double tmp = new Double(testSuite.getLevelSet()).doubleValue();
					row.createCell(cellIdx++).setCellValue(tmp);	
				} catch (Exception e) {
					row.createCell(cellIdx++).setCellValue(testSuite.getLevelSet().replace("\"", ""));
				}
				
			}else {
				row.createCell(cellIdx++).setCellValue("");
			}
			
			
			if(testSuite.getAnalogSet().length() != 0) {
				try {
					Double tmp = new Double(testSuite.getAnalogSet()).doubleValue();
					row.createCell(cellIdx++).setCellValue(tmp);	
				} catch (Exception e) {
					row.createCell(cellIdx++).setCellValue(testSuite.getAnalogSet().replace("\"", ""));
				}
				
			}else {
				row.createCell(cellIdx++).setCellValue("");
			}
			row.createCell(cellIdx++).setCellValue(testSuite.getTestFunction());
			row.createCell(cellIdx++).setCellValue(testSuite.getVectorLabel());
			
			row = sheet.createRow(rowIdx++);
		}
		//build footer
		if(hf.getConfigFile() != null && hf.getConfigFile().length() != 0) {
			row = sheet.createRow(rowIdx++);
			row.createCell((short) 0).setCellValue("config file:");
			row.createCell((short) 1).setCellValue(hf.getConfigFile());
			sheet.getRow(rowIdx - 1).getCell((short) 0).setCellStyle(style);
		}
		if(hf.getLevelFile() != null && hf.getLevelFile().length() != 0) {
			row = sheet.createRow(rowIdx++);
			row.createCell((short) 0).setCellValue("level file:");
			row.createCell((short) 1).setCellValue(hf.getLevelFile());
			sheet.getRow(rowIdx - 1).getCell((short) 0).setCellStyle(style);
		}
		if(hf.getTimingFile()!=null && hf.getTimingFile().length() != 0) {
			row = sheet.createRow(rowIdx++);
			row.createCell((short) 0).setCellValue("timing file:");
			row.createCell((short) 1).setCellValue(hf.getTimingFile());
			sheet.getRow(rowIdx - 1).getCell((short) 0).setCellStyle(style);
		}
		if(hf.getVectorFile() != null && hf.getVectorFile().length() != 0) {
			row = sheet.createRow(rowIdx++);
			row.createCell((short) 0).setCellValue("vector file:");
			row.createCell((short) 1).setCellValue(hf.getVectorFile());
			sheet.getRow(rowIdx - 1).getCell((short) 0).setCellStyle(style);
		}
		if(hf.getAttributeFile()!= null && hf.getAttributeFile().length() != 0) {
			row = sheet.createRow(rowIdx++);
			row.createCell((short) 0).setCellValue("attribute file:");
			row.createCell((short) 1).setCellValue(hf.getAttributeFile());
			sheet.getRow(rowIdx - 1).getCell((short) 0).setCellStyle(style);
		}
		if(hf.getAnalogFile() != null && hf.getAnalogFile().length() != 0) {
			row = sheet.createRow(rowIdx++);
			row.createCell((short) 0).setCellValue("analog control file:");
			row.createCell((short) 1).setCellValue(hf.getAnalogFile());
			sheet.getRow(rowIdx - 1).getCell((short) 0).setCellStyle(style);
		}
		if(hf.getWaveFormFile() != null && hf.getWaveFormFile().length() != 0) {
			row = sheet.createRow(rowIdx++);
			row.createCell((short) 0).setCellValue("wave form file:");
			row.createCell((short) 1).setCellValue(hf.getWaveFormFile());
			sheet.getRow(rowIdx - 1).getCell((short) 0).setCellStyle(style);
		}
		if(hf.getRoutingFile() != null && hf.getRoutingFile().length() != 0) {
			row = sheet.createRow(rowIdx++);
			row.createCell((short) 0).setCellValue("routing file:");
			row.createCell((short) 1).setCellValue(hf.getRoutingFile());
			sheet.getRow(rowIdx - 1).getCell((short) 0).setCellStyle(style);
		}
	}

	public void buildVDDSheet(Vector<VDDSummary> vddSummaries,
			Vector<VDDDetail> vddDetail) {
		//use for set bold style
		HSSFFont font = book.createFont();
		font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
		HSSFCellStyle style = book.createCellStyle();
		style.setFont(font);
		
		HSSFSheet sheet = book.createSheet("Vdd");
		sheet.setDefaultColumnWidth((short) 12);
//		sheet.setColumnWidth((short)7, (short)2000);
		
		sheet.createRow(0).createCell((short) 0).setCellValue("Equation, Spec");
		sheet.getRow(0).getCell((short) 0).setCellStyle(style);
		sheet.createRow(1).createCell((short) 0).setCellValue("Vdd");
		sheet.getRow(1).getCell((short) 0).setCellStyle(style);
		short colIdx = 1;
		for (VDDSummary summary : vddSummaries) {
			sheet.getRow(0).createCell(colIdx).setCellValue(summary.getEquation()+","+summary.getSpec());
			sheet.getRow(1).createCell(colIdx).setCellValue(summary.getVdd());
			sheet.getRow(1).getCell(colIdx++).setCellStyle(style);
		}
		//output label
		int rowIdx = 2;
		for(String vddKey : TestChipProcessing.VecAllVddKey) {
			sheet.createRow(rowIdx++).createCell((short) 0).setCellValue(vddKey);
		}
		
		rowIdx = 2;
		colIdx = 1;
		for (VDDDetail detail : vddDetail) {
			rowIdx = 2;
			for(String key : TestChipProcessing.VecAllVddKey) {
				if(detail.containKey(key)) {
					String value = detail.getDetail(key);
					if(value.length() != 0) {
						sheet.getRow(rowIdx++).createCell(colIdx).setCellValue(new Double(value));
					}else{
						sheet.getRow(rowIdx++).createCell(colIdx).setCellValue("");
					}
				}else {
					sheet.getRow(rowIdx++).createCell(colIdx).setCellValue("");
				}
				
			}
			colIdx++;
		}
	}
	
	public void flush() throws Exception {
		OutputStream out = new FileOutputStream(outputFile);
		book.write(out);
		out.flush();
		out.close();
	}
}
